﻿Imports System.Data
Imports VSModel
Imports Cls_Common
Imports SecurityService
Imports System.IO

Partial Class Control_UploadSoBienNhan_List
    Inherits System.Web.UI.UserControl
    Dim itm As New ListItem("--- Tất cả ---", "")
    Private Shared ReadOnly log As log4net.ILog = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType)
#Region "Sub and Function"
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim script As ScriptManager = ScriptManager.GetCurrent(Me.Page)
            If Not script Is Nothing AndAlso script.IsInAsyncPostBack Then
                ScriptManager.RegisterStartupScript(Me.Page, Me.Page.GetType, "jqueryEnable", "ajaxJquery()", True)
            Else
                Me.Page.ClientScript.RegisterStartupScript(Me.Page.GetType(), "jqueryEnable", String.Concat("Sys.Application.add_load(function(){", "ajaxJquery()", "});"), True)
            End If
            BindData()
            BindToGrid()
            btnDelete.Attributes.Add("onclick", "return confirmMultiDelete('" & btnDelete.ClientID & "');")
        End If
    End Sub
    Private Sub BindData()
        Using data As New VSEntities
            Dim _curHuyenId As Integer = Session("HuyenId")
            'load Citizen of dropdownlist
            Dim DanToc = (From q In data.Citizens Order By q.Name Descending Select q).ToList
            With ddlOw_nationality_id
                .DataSource = DanToc
                .DataValueField = "Name"
                .DataTextField = "Name"
                .DataBind()
                .Items.Insert(0, itm)
            End With
            'load DeptCertified of dropdownlist
            Dim PhongBanCap = (From q In data.DeptCertifieds Where q.ProvinceId = _curHuyenId Order By q.Name Select q).ToList
            With ddlCertifieddeptname
                .DataSource = PhongBanCap
                .DataValueField = "Id"
                .DataTextField = "Name"
                .DataBind()
                .Items.Insert(0, itm)
            End With
            'load DeptIdentifie of dropdownlist
            Dim CapCMND = (From q In data.DeptIdentifies Order By q.Name Select q).ToList
            With ddlOw_provider_id
                .DataSource = CapCMND
                .DataValueField = "Name"
                .DataTextField = "Name"
                .DataBind()
                .Items.Insert(0, itm)
            End With
            'load Province of dropdownlist
            'Dim Huyen = (From q In data.Provinces Order By q.Title Descending Select q).ToList
            'With ddlProvinceid
            '    .DataSource = Huyen
            '    .DataValueField = "Id"
            '    .DataTextField = "Title"
            '    .DataBind()
            '    .Items.Insert(0, itm)
            'End With
            'load CertifiedPaperDict of dropdownlist
            Dim GiayToKhac = (From q In data.CertifiedPaperDicts Order By q.Name Descending Select q).ToList
            With ddlOw_docofficertifiedid
                .DataSource = GiayToKhac
                .DataValueField = "Name"
                .DataTextField = "Name"
                .DataBind()
                .Items.Insert(0, itm)
            End With

            Dim p = (From q In data.Status Where q.FunctionId = Function_Name.CertifiedHousehold Order By q.StatusId Select q).ToList
            With ddlStatusId
                .DataSource = p
                .DataValueField = "StatusId"
                .DataTextField = "Title"
                .DataBind()
                .Items.Insert(0, New ListItem("---Tất cả---", -1))
            End With

        End Using
    End Sub
    Private Sub BindToGrid(Optional ByVal iPage As Integer = 1 _
          , Optional ByVal strCertifiedCode As String = "" _
            , Optional ByVal dtmCertifiedDateFrom As Date = #1/1/1900# _
            , Optional ByVal dtmCertifiedDateTo As Date = #12/31/3999# _
            , Optional ByVal strBiz_VietName As String = "" _
            , Optional ByVal strOw_Name As String = "" _
            , Optional ByVal intOw_Sex As Integer = -1 _
            , Optional ByVal strOw_BirthDate As String = "" _
            , Optional ByVal strOw_Nationality_ID As String = "" _
            , Optional ByVal strOw_CardID As String = "" _
            , Optional ByVal dtmOw_CardDateFrom As Date = #1/1/1900# _
            , Optional ByVal dtmOw_CardDateTo As Date = #12/31/3999# _
            , Optional ByVal strOw_Provider_ID As String = "" _
            , Optional ByVal strOw_Tel As String = "" _
            , Optional ByVal strOw_Email As String = "" _
            , Optional ByVal strOw_WebSite As String = "" _
            , Optional ByVal strBiz_HeadOffice As String = "" _
            , Optional ByVal strOw_Present As String = "" _
            , Optional ByVal strCertifiedDeptName As String = "" _
            , Optional ByVal strCertifiedSignerName As String = "" _
            , Optional ByVal strCertifiedStatus As String = "" _
            , Optional ByVal intCertifiedIndex As Integer = -1 _
            , Optional ByVal strOw_TaxCode As String = "" _
            , Optional ByVal intProvinceId As Integer = -1 _
            , Optional ByVal intStatusId As Integer = -1 _
    )

        intProvinceId = Session("HuyenId")
        'intStatusId = TinhTrangHoSo.ChoDuyet

        Using data As New VSEntities
            Dim arrSearch() As String = {iPage.ToString, strCertifiedCode.ToString, dtmCertifiedDateFrom, dtmCertifiedDateTo _
                                        , strBiz_VietName.ToString, strOw_Name.ToString, intOw_Sex.ToString, strOw_BirthDate.ToString _
                                        , strOw_Nationality_ID.ToString, strOw_CardID.ToString, dtmOw_CardDateFrom.ToString _
                                        , dtmOw_CardDateTo.ToString, strOw_Provider_ID.ToString, strOw_Tel.ToString, strOw_Email.ToString _
                                        , strOw_WebSite.ToString, strBiz_HeadOffice.ToString, strOw_Present.ToString _
                                        , strCertifiedDeptName.ToString, strCertifiedSignerName.ToString, strCertifiedStatus.ToString _
                                        , intCertifiedIndex.ToString, strOw_TaxCode.ToString, intProvinceId.ToString, intStatusId.ToString}
            ViewState("search") = arrSearch
            'So ban ghi muon the hien tren trang
            Dim intPag_Size As Int32 = drpPage_Size.SelectedValue
            intStatusId = ddlStatusId.SelectedValue
            strCertifiedStatus = ddlLoaiHoSo.SelectedValue

            Dim p As List(Of uspCertifiedHouseholdSelectAll_Result) = data.uspCertifiedHouseholdSelectAll(strCertifiedCode _
                    , dtmCertifiedDateFrom _
                    , dtmCertifiedDateTo _
                    , strBiz_VietName _
                    , strOw_Name _
                    , intOw_Sex _
                    , strOw_Nationality_ID _
                    , strOw_CardID _
                    , dtmOw_CardDateFrom _
                    , dtmOw_CardDateTo _
                    , strOw_Provider_ID _
                    , strOw_Tel _
                    , strOw_Email _
                    , strOw_WebSite _
                    , strBiz_HeadOffice _
                    , strOw_Present _
                    , strCertifiedDeptName _
                    , strCertifiedSignerName _
                    , strCertifiedStatus _
                    , strOw_TaxCode _
                    , intStatusId _
                    , intProvinceId _
                    , iPage _
                    , intPag_Size).ToList
            Dim strKey_Name() As String = {"CertifiedID", "CertifiedCode", "CertifiedStatus", "StatusId", "Ow_Sex", "Biz_VietName"}
            'Tong so ban ghi
            If p.Count > 0 Then
                hidCount.Value = p.FirstOrDefault.Total()
                Create_Pager(hidCount.Value, iPage, intPag_Size, 10)
            Else
                hidCount.Value = 0
                With rptPage
                    .DataSource = Nothing
                    .DataBind()
                End With
            End If
            With grdShow
                .DataKeyNames = strKey_Name
                .DataSource = p
                .DataBind()
            End With
            If (hidCount.Value > 0) Then
                lblTotal.Text = "Hiển thị " + (((iPage - 1) * intPag_Size) + 1).ToString + " đến " + (((iPage - 1) * intPag_Size) + grdShow.Rows.Count).ToString + " trong tổng số " + hidCount.Value.ToString + " bản ghi."
            Else
                lblTotal.Text = ""
            End If
        End Using
    End Sub
    Sub Create_Pager(ByVal Total_Record As Integer, ByVal Page_Index As Integer, ByVal Page_Size As Integer, ByVal Page2Show As Integer)
        Dim TotalPage As Integer = IIf((Total_Record Mod Page_Size) = 0, Total_Record / Page_Size, Total_Record \ Page_Size + 1)
        'lu lai tong so ban ghi
        hidIndex_page.Value = TotalPage
        'gan lai curPage de set active
        hidCur_Page.Value = Page_Index
        'generate ra left page
        Dim cPageGenerate_left As IEnumerable(Of Integer)
        If Page_Index <= Page2Show Then
            cPageGenerate_left = Enumerable.Range(1, Page_Index)
        Else
            cPageGenerate_left = Enumerable.Range(Page_Index - Page2Show, Page2Show)
        End If
        'generate ra right page
        Dim cPageGenerate_Right As IEnumerable(Of Integer)
        If Page_Index + Page2Show <= TotalPage Then
            cPageGenerate_Right = Enumerable.Range(Page_Index, Page2Show + 1)
        Else
            cPageGenerate_Right = Enumerable.Range(Page_Index, TotalPage - Page_Index + 1)
        End If
        'union 2 range va bind to Grid
        With rptPage
            .DataSource = cPageGenerate_left.Union(cPageGenerate_Right)
            .DataBind()
        End With
    End Sub
    Protected Sub rptPage_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles rptPage.ItemDataBound
        Dim lnkTitle As LinkButton
        lnkTitle = e.Item.FindControl("lnkTitle")
        Dim ScriptManager As System.Web.UI.ScriptManager = System.Web.UI.ScriptManager.GetCurrent(Me.Page)
        ScriptManager.RegisterAsyncPostBackControl(lnkTitle)
        If e.Item.DataItem = hidCur_Page.Value Then
            lnkTitle.Text = "<span class='current'>" & e.Item.DataItem & "</span>"
        Else
            lnkTitle.Text = "<span>" & e.Item.DataItem & "</span>"
        End If
        lnkTitle.ToolTip = e.Item.DataItem
    End Sub
    Protected Sub lnkTitle_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim lnkTile As LinkButton = CType(sender, LinkButton)
        Dim arrSearch() As String
        arrSearch = ViewState("search")
        BindToGrid(CInt(lnkTile.ToolTip) _
       , arrSearch(1) _
       , arrSearch(2) _
       , arrSearch(3) _
       , arrSearch(4) _
       , arrSearch(5) _
       , arrSearch(6) _
       , arrSearch(7) _
       , arrSearch(8) _
       , arrSearch(9) _
       , arrSearch(10) _
       , arrSearch(11) _
       , arrSearch(12) _
       , arrSearch(13) _
       , arrSearch(14) _
       , arrSearch(15) _
       , arrSearch(16) _
       , arrSearch(17) _
       , arrSearch(18) _
       , arrSearch(19) _
       , arrSearch(20) _
       , arrSearch(21) _
       , arrSearch(22) _
       , arrSearch(23) _
            )
        lnkLast.Enabled = True
        lnkFirst.Enabled = True
        If CInt(lnkTile.ToolTip) = hidIndex_page.Value Then
            lnkLast.Enabled = False
        ElseIf CInt(lnkTile.ToolTip) = 1 Then
            lnkFirst.Enabled = False
        End If
    End Sub
    Protected Sub lnkFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFirst.Click
        If hidCur_Page.Value > 1 Then
            hidCur_Page.Value = hidCur_Page.Value - 1
        End If
        BindToGrid(hidCur_Page.Value)
    End Sub
    Protected Sub lnkLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkLast.Click
        hidCur_Page.Value = hidCur_Page.Value + 1
        BindToGrid(hidCur_Page.Value)
    End Sub
#End Region
#Region "Event for control"
    Protected Sub lnkbtnDelete_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim intId As Integer
        Dim strLogName As String = ""
        Using data As New VSEntities
            intId = grdShow.DataKeys(hidID.Value)("CertifiedID").ToString
            Dim q = (From p In data.CertifiedHouseholds Where p.CertifiedID = intId Select p).FirstOrDefault
            Try
                data.CertifiedHouseholds.DeleteObject(q)
                Dim strGCN As String = ""
                If Not q.CertifiedCode Is Nothing Then
                    strGCN = q.CertifiedCode
                End If
                Dim strLog As String = "Giay CN:" & strGCN & ", tên hộ: " & q.Biz_VietName & ", nguoi dai dien: " & q.Ow_Name & ", CMND: " & q.Ow_CardID _
                                       & ", địa chỉ kinh doanh: " & q.Biz_HeadOffice
                Insert_App_Log("Xóa  Certified:" & strLog & "", Function_Name.CertifiedHousehold, Audit_Type.Delete, Request.ServerVariables("REMOTE_ADDR"), Session("UserName"))
                data.SaveChanges()
                Excute_Javascript("Alertbox('Xóa dữ liệu thành công.');", Me.Page, True)
            Catch ex As Exception
                log4net.Config.XmlConfigurator.Configure()
                log.Error("Error error " & AddTabSpace(1) & Session("Username") & AddTabSpace(1) & "IP:" & GetIPAddress(), ex)
                Excute_Javascript("Alertbox('Xóa thất bại.');", Me.Page, True)
            End Try
        End Using
        Dim arrSearch() As String
        arrSearch = ViewState("search")
        BindToGrid(1 _
       , arrSearch(1) _
       , arrSearch(2) _
       , arrSearch(3) _
       , arrSearch(4) _
       , arrSearch(5) _
       , arrSearch(6) _
       , arrSearch(7) _
       , arrSearch(8) _
       , arrSearch(9) _
       , arrSearch(10) _
       , arrSearch(11) _
       , arrSearch(12) _
       , arrSearch(13) _
       , arrSearch(14) _
       , arrSearch(15) _
       , arrSearch(16) _
       , arrSearch(17) _
       , arrSearch(18) _
       , arrSearch(19) _
       , arrSearch(20) _
       , arrSearch(21) _
       , arrSearch(22) _
       , arrSearch(23) _
        )
    End Sub
    Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim intId As Integer = 0
        Dim intCount As Integer
        Dim intTotal As Integer
        Using data As New VSEntities
            Try
                For Each item As GridViewRow In grdShow.Rows
                    Dim chkItem As New CheckBox
                    chkItem = CType(item.FindControl("chkItem"), CheckBox)
                    If chkItem.Checked Then
                        intTotal += 1
                        intId = grdShow.DataKeys(item.RowIndex)("CertifiedID").ToString
                        Dim q = (From p In data.CertifiedHouseholds Where p.CertifiedID = intId Select p).FirstOrDefault
                        Try
                            data.CertifiedHouseholds.DeleteObject(q)
                            Dim strGCN As String = ""
                            If Not q.CertifiedCode Is Nothing Then
                                strGCN = q.CertifiedCode
                            End If
                            Dim strLog As String = "Giay CN:" & strGCN & ", tên hộ: " & q.Biz_VietName & ", nguoi dai dien: " & q.Ow_Name & ", CMND: " & q.Ow_CardID _
                                                   & ", địa chỉ kinh doanh: " & q.Biz_HeadOffice
                            Insert_App_Log("Xóa  Certified:" & strLog & "", Function_Name.CertifiedHousehold, Audit_Type.Delete, Request.ServerVariables("REMOTE_ADDR"), Session("UserName"))

                            data.SaveChanges()
                            intCount += 1
                        Catch ex As Exception
                        End Try
                    End If
                Next
                If intCount > 0 Then
                    Excute_Javascript("Alertbox('Xóa dữ liệu thành công. " & intCount.ToString & " /" & intTotal.ToString & " record.');", Me.Page, True)
                Else
                    Excute_Javascript("Alertbox('Xóa thất bại.');", Me.Page, True)
                End If
            Catch ex As Exception
                log4net.Config.XmlConfigurator.Configure()
                log.Error("Error error " & AddTabSpace(1) & Session("Username") & AddTabSpace(1) & "IP:" & GetIPAddress(), ex)
                Excute_Javascript("Alertbox('Xóa thất bại.');", Me.Page, True)
            End Try
        End Using
        BindToGrid()
    End Sub
    Protected Sub grdShow_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles grdShow.RowDataBound
        If e.Row.RowIndex >= 0 Then
            Dim status As Integer = grdShow.DataKeys(e.Row.RowIndex)("StatusId")
            Dim lblSTT As Label = CType(e.Row.FindControl("lblSTT"), Label)
            lblSTT.Text = CInt(drpPage_Size.SelectedValue) * (CInt(hidCur_Page.Value) - 1).ToString + e.Row.RowIndex + 1


            Dim lnkbtnDelete As LinkButton = CType(e.Row.FindControl("lnkbtnDelete"), LinkButton)

            Dim ScriptManager As System.Web.UI.ScriptManager = System.Web.UI.ScriptManager.GetCurrent(Me.Page)
            ScriptManager.RegisterAsyncPostBackControl(lnkbtnDelete)

            Dim chkItem As CheckBox = CType(e.Row.FindControl("chkItem"), CheckBox)
            'Permission
            If HasPermission(Function_Name.CertifiedHousehold, Session("RoleID"), status, Audit_Type.Delete) = True Then
                lnkbtnDelete.Attributes.Add("onclick", "return ComfirmDialog('" + drpMessage.Items(0).Text + "', 0,'" + lnkbtnDelete.ClientID + "','" + e.Row.RowIndex.ToString + "',1);")
            Else
                lnkbtnDelete.Enabled = False
                chkItem.Enabled = False
            End If

            Dim hplEdit As HyperLink = CType(e.Row.FindControl("hplEdit"), HyperLink)
            'Permission
            hplEdit.Enabled = HasPermission(Function_Name.CertifiedHousehold, Session("RoleID"), status, Audit_Type.Edit)
            hplEdit.NavigateUrl = "../../Page/UploadSoBienNhan/Edit.aspx?Certifiedid=" & grdShow.DataKeys(e.Row.RowIndex)("CertifiedID").ToString

            Dim hplBiz_VietName As HyperLink = CType(e.Row.FindControl("hplBiz_VietName"), HyperLink)
            hplBiz_VietName.NavigateUrl = "../../Page/UploadSoBienNhan/Detail.aspx?Certifiedid=" & grdShow.DataKeys(e.Row.RowIndex)("CertifiedID").ToString
            hplBiz_VietName.Text = grdShow.DataKeys(e.Row.RowIndex)("Biz_VietName").ToString
            'Permission
            '  hplBiz_VietName.Enabled = HasPermission(Function_Name.CertifiedHousehold, Session("RoleID"), status, Audit_Type.ViewContent)



            Using data As New VSEntities
                Dim lblStatusId As Label = CType(e.Row.FindControl("lblStatusId"), Label)
                Dim _l As Integer = grdShow.DataKeys(e.Row.RowIndex)("StatusId")
                lblStatusId.Text = (From h In data.Status Where h.StatusId = _l Select h.TitleHoSo).FirstOrDefault
                lblStatusId.CssClass = "hoso_" & _l.ToString

                Dim lblNgayNop As Label = CType(e.Row.FindControl("lblNgayNop"), Label)
                'Dim lblNgayTra As Label = CType(e.Row.FindControl("lblNgayTra"), Label)
                'Dim _cerId As Integer = grdShow.DataKeys(e.Row.RowIndex)("CertifiedID").ToString
                'Dim _nn = (From q In data.CertifiedHouseholds Where q.CertifiedID = _cerId Select q).FirstOrDefault
                ''luu y ngay nop se tinh lai
                'If Not _nn Is Nothing AndAlso Not _nn.Created Is Nothing Then
                '    'ngay nop
                '    Dim ngayNop As Date = CType(_nn.Created, DateTime)
                '    lblNgayNop.Text = ngayNop.ToString("dd/MM/yyyy")
                '    ' ngay tra
                '    Dim ngayTra As DateTime = CType(_nn.NgayTraHoSo, Date)
                '    lblNgayTra.Text = ngayTra.ToString("dd/MM/yyyy")
                'End If


                'số biên nhận
                Dim _CertifiedID As Integer = grdShow.DataKeys(e.Row.RowIndex)("CertifiedID")
                Dim pBienNhan = (From i In data.CertifiedHouseholds Where i.CertifiedID = _CertifiedID Select i).FirstOrDefault
                Dim lblSoBienNhan As Label = CType(e.Row.FindControl("lblSoBienNhan"), Label)
                lblSoBienNhan.Text = pBienNhan.SoBienNhan
            End Using

            Dim lblGioTinh As Label = CType(e.Row.FindControl("lblGioTinh"), Label)
            lblGioTinh.Text = IIf(grdShow.DataKeys(e.Row.RowIndex)("Ow_Sex") = 1, "Nam", "Nữ")


        End If
    End Sub
#End Region
#Region "Search"
    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim certFrom As DateTime = IIf(txtCertifieddateFrom.Text.Trim <> "", StringToDate(txtCertifieddateFrom.Text.Trim), "1900/01/01")
        Dim certTo As DateTime = IIf(txtCertifieddateTo.Text.Trim <> "", StringToDate(txtCertifieddateTo.Text.Trim), "3999/12/31")
        Dim CardFrom As DateTime = IIf(txtOw_carddateFrom.Text.Trim <> "", StringToDate(txtOw_carddateFrom.Text.Trim), "1900/01/01")
        Dim CardTo As DateTime = IIf(txtOw_carddateTo.Text.Trim <> "", StringToDate(txtOw_carddateTo.Text.Trim), "3999/12/31")

        BindToGrid(1 _
            , txtCertifiedcode.Text.Trim _
            , certFrom _
            , certTo _
            , txtBiz_vietname.Text.Trim _
            , txtOw_name.Text.Trim _
            , ddlOw_sex.SelectedValue _
            , txtOw_birthdate.Text.Trim _
            , ddlOw_nationality_id.SelectedValue _
            , txtOw_cardid.Text.Trim _
            , CardFrom _
            , CardTo _
            , ddlOw_provider_id.SelectedValue _
            , txtOw_tel.Text.Trim _
            , txtOw_fax.Text.Trim _
            , txtOw_email.Text.Trim _
            , txtBiz_HeadOffice.Text.Trim _
            , "" _
            , txtCertifiedsignername.Text.Trim _
            , ddlCertifieddeptname.SelectedValue _
            , ddlLoaiHoSo.SelectedValue _
            )

    End Sub
    Protected Sub drpPage_Size_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles drpPage_Size.SelectedIndexChanged
        Dim arrSearch() As String
        arrSearch = ViewState("search")
        BindToGrid(1, arrSearch(1) _
       , arrSearch(2) _
       , arrSearch(3) _
       , arrSearch(4) _
       , arrSearch(5) _
       , arrSearch(6) _
       , arrSearch(7) _
       , arrSearch(8) _
       , arrSearch(9) _
       , arrSearch(10) _
       , arrSearch(11) _
       , arrSearch(12) _
       , arrSearch(13) _
       , arrSearch(14) _
       , arrSearch(15) _
       , arrSearch(16) _
       , arrSearch(17) _
       , arrSearch(18) _
       , arrSearch(19) _
       , arrSearch(20) _
       , arrSearch(21) _
       , arrSearch(22) _
       , arrSearch(23) _
         )
    End Sub
    Protected Sub btnReset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnReset.Click
        txtCertifiedcode.Text = ""
        txtCertifieddateFrom.Text = ""
        txtCertifieddateTo.Text = ""
        txtBiz_vietname.Text = ""
        txtOw_name.Text = ""
        ddlOw_sex.SelectedIndex = -1
        txtOw_birthdate.Text = ""
        'txtOw_national_id.Text = ""
        ddlOw_nationality_id.SelectedIndex = -1
        txtOw_cardid.Text = ""
        txtOw_carddateFrom.Text = ""
        txtOw_carddateTo.Text = ""
        ddlOw_provider_id.SelectedIndex = -1
        ddlOw_docofficertifiedid.SelectedIndex = -1
        txtOw_tel.Text = ""
        txtOw_fax.Text = ""
        txtOw_email.Text = ""
        txtBiz_HeadOffice.Text = ""
        txtOw_present.Text = ""
        ddlCertifieddeptname.SelectedIndex = -1
        txtCertifiedsignername.Text = ""
        ddlStatusId.SelectedIndex = -1
        txtOw_taxcode.Text = ""
        drpPage_Size.SelectedIndex = -1
        BindToGrid(1)
    End Sub
#End Region

    Protected Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportExcel.Click

        Try

            Dim _fieldName As String() = {"RowNum", "CertifiedCode", "CertifiedDate", "Biz_HeadOffice", "Ow_Name", "Biz_VietName", "NganhNgheKinhDoanh", "Biz_InvestCapital" _
                                         , "Ow_Resident", "Ow_CardID", "Ow_CardDate", "Ow_Provider_ID", "TinhTrang", "Ow_TaxCode", "NgayCapNhatMST", "NgayNgungMST"}

            Using data As New VSEntities
                Dim _fileName As String = "HoKinhDoanh_" & Date.Now.ToFileTimeUtc()
                Dim certFrom As DateTime = IIf(txtCertifieddateFrom.Text.Trim <> "", StringToDate(txtCertifieddateFrom.Text.Trim), "1900/01/01")
                Dim certTo As DateTime = IIf(txtCertifieddateTo.Text.Trim <> "", StringToDate(txtCertifieddateTo.Text.Trim), "3999/12/31")
                Dim CardFrom As DateTime = IIf(txtOw_carddateFrom.Text.Trim <> "", StringToDate(txtOw_carddateFrom.Text.Trim), "1900/01/01")
                Dim CardTo As DateTime = IIf(txtOw_carddateTo.Text.Trim <> "", StringToDate(txtOw_carddateTo.Text.Trim), "3999/12/31")


                Dim intProvinceId As Integer = Session("HuyenId")

                Dim p = ExportToExcel()

                Dim applicationPath As String = Server.MapPath(Request.ApplicationPath)
                If Not applicationPath.EndsWith("\") Then
                    applicationPath = applicationPath + "\"
                End If
                Dim licenseFile As String = Path.Combine(applicationPath, "bin\Aspose.Cells.lic")
                Dim license As Aspose.Cells.License = New Aspose.Cells.License()
                license.SetLicense(licenseFile)
                Dim _workbook As New Aspose.Cells.Workbook(Server.MapPath("~/Template/HoKinhDoanh.xls"))
                Dim _workSheet As Aspose.Cells.Worksheet = _workbook.Worksheets(0)
                _workSheet.Cells.ImportCustomObjects(CType(p, System.Collections.ICollection), _fieldName, False, 1, 0, p.Count, True, "dd/MM/yyyy", False)
                _workSheet.AutoFitColumns()
                Cls_Common.CreateDir(Server.MapPath("~/Template/ReportDownload/"))
                _workbook.Save(Server.MapPath("~/Template/ReportDownload/" & _fileName & ".xls"))
                Excute_Javascript("Alertbox('Xuất dữ liệu thành công'); SetDownFile('" & ResolveUrl("~/Template/ReportDownload/" & _fileName & ".xls") & "')", Me.Page, True)

            End Using
        Catch ex As Exception
            log4net.Config.XmlConfigurator.Configure()
            log.Error("Error error " & AddTabSpace(1) & Session("Username") & AddTabSpace(1) & "IP:" & GetIPAddress(), ex)
            Excute_Javascript("Alertbox('Xuất dữ liệu thất bại." & ex.Message & "');", Me.Page, True)
        End Try


    End Sub

    Private Function ExportToExcel(Optional ByVal iPage As Integer = 1 _
       , Optional ByVal strCertifiedCode As String = "" _
         , Optional ByVal dtmCertifiedDateFrom As Date = #1/1/1900# _
         , Optional ByVal dtmCertifiedDateTo As Date = #12/31/3999# _
         , Optional ByVal strBiz_VietName As String = "" _
         , Optional ByVal strOw_Name As String = "" _
         , Optional ByVal intOw_Sex As Integer = -1 _
         , Optional ByVal strOw_Nationality_ID As String = "" _
         , Optional ByVal strOw_CardID As String = "" _
         , Optional ByVal dtmOw_CardDateFrom As Date = #1/1/1900# _
         , Optional ByVal dtmOw_CardDateTo As Date = #12/31/3999# _
         , Optional ByVal strOw_Provider_ID As String = "" _
         , Optional ByVal strOw_Tel As String = "" _
         , Optional ByVal strOw_Email As String = "" _
         , Optional ByVal strOw_WebSite As String = "" _
         , Optional ByVal strBiz_HeadOffice As String = "" _
         , Optional ByVal strOw_Present As String = "" _
         , Optional ByVal strCertifiedDeptName As String = "" _
         , Optional ByVal strCertifiedSignerName As String = "" _
         , Optional ByVal strCertifiedStatus As String = "" _
         , Optional ByVal strOw_TaxCode As String = "" _
         , Optional ByVal intStatusId As Integer = -1 _
         , Optional ByVal intProvinceId As Integer = -1 _
 ) As List(Of uspCertifiedHouseholdReportExcel_Result)

        intProvinceId = Session("HuyenId")
        'intStatusId = TinhTrangHoSo.ChoDuyet


        Using data As New VSEntities
            Dim arrSearch() As String = {iPage.ToString, strCertifiedCode.ToString, dtmCertifiedDateFrom, dtmCertifiedDateTo _
                                        , strBiz_VietName.ToString, strOw_Name.ToString, intOw_Sex.ToString _
                                        , strOw_Nationality_ID.ToString, strOw_CardID.ToString, dtmOw_CardDateFrom.ToString _
                                        , dtmOw_CardDateTo.ToString, strOw_Provider_ID.ToString, strOw_Tel.ToString, strOw_Email.ToString _
                                        , strOw_WebSite.ToString, strBiz_HeadOffice.ToString, strOw_Present.ToString _
                                        , strCertifiedDeptName.ToString, strCertifiedSignerName.ToString, strCertifiedStatus.ToString _
                                        , strOw_TaxCode.ToString, intStatusId.ToString, intProvinceId.ToString}
            ViewState("search") = arrSearch
            'So ban ghi muon the hien tren trang
            Dim intPag_Size As Int32 = drpPage_Size.SelectedValue
            intStatusId = ddlStatusId.SelectedValue
            strCertifiedStatus = ddlLoaiHoSo.SelectedValue

            Dim p As List(Of uspCertifiedHouseholdReportExcel_Result) = data.uspCertifiedHouseholdReportExcel(strCertifiedCode _
                    , dtmCertifiedDateFrom _
                    , dtmCertifiedDateTo _
                    , strBiz_VietName _
                    , strOw_Name _
                    , intOw_Sex _
                    , strOw_Nationality_ID _
                    , strOw_CardID _
                    , dtmOw_CardDateFrom _
                    , dtmOw_CardDateTo _
                    , strOw_Provider_ID _
                    , strOw_Tel _
                    , strOw_Email _
                    , strOw_WebSite _
                    , strBiz_HeadOffice _
                    , strOw_Present _
                    , strCertifiedDeptName _
                    , strCertifiedSignerName _
                    , strCertifiedStatus _
                    , strOw_TaxCode _
                    , intStatusId _
                    , intProvinceId _
                    , iPage _
                    , 1000000).ToList
            Return p
        End Using
    End Function
End Class
